Home » SQL & PL/SQL » SQL & PL/SQL » String Parsing
String Parsing [message #7953] |
Fri, 18 July 2003 09:23 |
MM
Messages: 27 Registered: July 2002
|
Junior Member |
|
|
I have a string 'SCWID,CWT,COT,ACB,AR' this is stored in a columns called sw_features. I have a select for add and delete new features which come in. I dont have a check in case the existing one comes for add or the non existing one comes for delete.
My question is in case of adding how do i check the new feature coming in which may be one in the string 'SCWID,CWT,COT,ACB,AR' and in case of delete how do i try not to delete something which is not there.
If you more questions let me know.
Thanks in advance
|
|
|
Re: String Parsing [message #7954 is a reply to message #7953] |
Fri, 18 July 2003 09:54 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
MM,SQL> SET VERIFY OFF
SQL> DECLARE
2 l_delim VARCHAR2(1) := ',';
3 l_string VARCHAR2(100) := 'SCWID,CWT,COT,ACB,AR';
4 l_candidate VARCHAR2(20) := '&candidate';
5 l_keyword VARCHAR2(4);
6 BEGIN
7 IF (INSTR(l_delim || l_string || l_delim
8 , l_delim || l_candidate || l_delim) = 0) THEN
9 l_keyword := 'not ';
10 END IF;
11 DBMS_OUTPUT.PUT_LINE('"'
12 || l_candidate
13 || '" is '
14 || l_keyword
15 || 'in "'
16 || l_string
17 || '".');
18 END;
19 /
Enter value for candidate: COT
"COT" is in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: JUNK
"JUNK" is not in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: AR
"AR" is in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: CWID
"CWID" is not in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: SCWID
"SCWID" is in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: W
"W" is not in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: ABC
"ABC" is not in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> /
Enter value for candidate: ACB
"ACB" is in "SCWID,CWT,COT,ACB,AR".
PL/SQL procedure successfully completed.
SQL> In the INSTR function, I prefix and suffix both your database string ("l_string") and the candidate string ("l_candidate") with the delimiter to prevent false positives. (Without this step, "CWID" would be considered a "hit", when in fact it is not.)
Good luck,
A.
|
|
|
|
Re: String Parsing [message #7968 is a reply to message #7955] |
Fri, 18 July 2003 16:00 |
MM
Messages: 27 Registered: July 2002
|
Junior Member |
|
|
Art,
This function returns a numeric value 1 or 0, Is there a way to get the alphanumeric value like 'SCWID'
Thanks,
MM
|
|
|
Re: String Parsing [message #7989 is a reply to message #7968] |
Mon, 21 July 2003 05:52 |
Art Metzer
Messages: 2480 Registered: December 2002
|
Senior Member |
|
|
quote from MM:
----------------------------------------------------------------------
Art,
This function returns a numeric value 1 or 0, Is there a way to get the alphanumeric value like 'SCWID'
Thanks,
MM
----------------------------------------------------------------------
Sure, but what are your inputs? What's your desired output if your code isn't found?
A.
|
|
|
Goto Forum:
Current Time: Tue May 21 11:00:37 CDT 2024
|